/****** Object:  Stored Procedure ShippingStatusReportDelete    Script Date: Sunday, April 30, 2006 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spShippingStatusReportDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spShippingStatusReportDelete]
GO

/****** Object:  Stored Procedure ShippingStatusReportGet    Script Date: Sunday, April 30, 2006 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spShippingStatusReportGet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spShippingStatusReportGet]
GO

/****** Object:  Stored Procedure ShippingStatusReportGetList    Script Date: Sunday, April 30, 2006 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spShippingStatusReportGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spShippingStatusReportGetList]
GO

	
/****** Object:  Stored Procedure ShippingStatusReportInsert    Script Date: Sunday, April 30, 2006 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spShippingStatusReportInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spShippingStatusReportInsert]
GO

/****** Object:  Stored Procedure ShippingStatusReportUpdate    Script Date: Sunday, April 30, 2006 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spShippingStatusReportUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spShippingStatusReportUpdate]
GO

/******************************************************************************
**		File: 
**		Name: spShippingStatusReportDelete
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 4/30/2006 10:58:36 AM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE spShippingStatusReportDelete
	@ID int
AS

DELETE FROM [ShippingStatusReport]
WHERE
	[ID] = @ID
GO
	
/******************************************************************************
**		File: 
**		Name: spShippingStatusReportGet
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 4/30/2006 10:58:36 AM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE spShippingStatusReportGet
	@ID int
AS

SELECT
	[ID],
	[REQ],
	[OrderDate],
	[ContainerSize],
	[LP],
	[PONo_Office],
	[PONo_Type],
	[PONo_Supplier],
	[PONo_Number],
	[IsPrimary],
	[PONo_Consolidation],
	[AccNo],
	[CustomerOrLocation],
	[FinalDestination],
	[Description],
	[FactoryETD],
	[ShippingDate],
	[ShippingLine],
	[Vessel],
	[ContainerNo],
	[ETA],
	[Week],
	[ArrivalPort],
	[PI],
	[Payment],
	[DocReceived],
	[CompletedDate],
	[BulkType],
	[ModifiedDate]
FROM
	[ShippingStatusReport]
WHERE
	[ID] = @ID

GO
	
/******************************************************************************
**		File: 
**		Name: spShippingStatusReportGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 4/30/2006 10:58:36 AM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE spShippingStatusReportGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempShippingStatusReport (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ID int	
)

DECLARE @sql nvarchar(2000)

-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempShippingStatusReport ([ID]) SELECT '
IF (@PageSize > 0)
	SET @sql = @sql + ' TOP ' + CAST(@Page*@PageSize as nvarchar) 

SET @sql = @sql + ' [ID] FROM [ShippingStatusReport]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection

EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [ShippingStatusReport]

SELECT
	[ShippingStatusReport].[ID],
	[REQ],
	[OrderDate],
	[ContainerSize],
	[LP],
	[PONo_Office],
	[PONo_Type],
	[PONo_Supplier],
	[PONo_Number],
	[IsPrimary],
	[PONo_Consolidation],
	[AccNo],
	[CustomerOrLocation],
	[FinalDestination],
	[Description],
	[FactoryETD],
	[ShippingDate],
	[ShippingLine],
	[Vessel],
	[ContainerNo],
	[ETA],
	[Week],
	[ArrivalPort],
	[PI],
	[Payment],
	[DocReceived],
	[CompletedDate],
	[BulkType],
	[ModifiedDate]
FROM
	#TempShippingStatusReport AS tblTemp JOIN [ShippingStatusReport] ON
	tblTemp.ID = [ShippingStatusReport].ID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempShippingStatusReport

GO

/******************************************************************************
**		File: 
**		Name: spShippingStatusReportInsert
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 4/30/2006 10:58:36 AM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE spShippingStatusReportInsert
	@REQ int,
	@OrderDate datetime,
	@ContainerSize varchar(50),
	@LP char(1),
	@PONo_Office char(4),
	@PONo_Type char(1),
	@PONo_Supplier int,
	@PONo_Number int,
	@IsPrimary char(1),
	@PONo_Consolidation int,
	@AccNo varchar(6),
	@CustomerOrLocation nvarchar(30),
	@FinalDestination nvarchar(30),
	@Description nvarchar(50),
	@FactoryETD datetime,
	@ShippingDate datetime,
	@ShippingLine varchar(255),
	@Vessel nvarchar(30),
	@ContainerNo varchar(16),
	@ETA datetime,
	@Week int,
	@ArrivalPort nvarchar(30),
	@PI varchar(12),
	@Payment char(1),
	@DocReceived bit,
	@CompletedDate datetime,
	@BulkType varchar(2),
	@ModifiedDate datetime,
	@ID int output
AS

INSERT INTO [ShippingStatusReport] 
(
	[REQ],
	[OrderDate],
	[ContainerSize],
	[LP],
	[PONo_Office],
	[PONo_Type],
	[PONo_Supplier],
	[PONo_Number],
	[IsPrimary],
	[PONo_Consolidation],
	[AccNo],
	[CustomerOrLocation],
	[FinalDestination],
	[Description],
	[FactoryETD],
	[ShippingDate],
	[ShippingLine],
	[Vessel],
	[ContainerNo],
	[ETA],
	[Week],
	[ArrivalPort],
	[PI],
	[Payment],
	[DocReceived],
	[CompletedDate],
	[BulkType],
	[ModifiedDate]
) 
VALUES 
(
	@REQ,
	@OrderDate,
	@ContainerSize,
	@LP,
	@PONo_Office,
	@PONo_Type,
	@PONo_Supplier,
	@PONo_Number,
	@IsPrimary,
	@PONo_Consolidation,
	@AccNo,
	@CustomerOrLocation,
	@FinalDestination,
	@Description,
	@FactoryETD,
	@ShippingDate,
	@ShippingLine,
	@Vessel,
	@ContainerNo,
	@ETA,
	@Week,
	@ArrivalPort,
	@PI,
	@Payment,
	@DocReceived,
	@CompletedDate,
	@BulkType,
	@ModifiedDate
)

SELECT @ID = @@IDENTITY

GO
	
/******************************************************************************
**		File: 
**		Name: spShippingStatusReportUpdate
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 4/30/2006 10:58:36 AM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE spShippingStatusReportUpdate
	@ID int, 
	@REQ int, 
	@OrderDate datetime, 
	@ContainerSize varchar(50), 
	@LP char(1), 
	@PONo_Office char(4), 
	@PONo_Type char(1), 
	@PONo_Supplier int, 
	@PONo_Number int, 
	@IsPrimary char(1), 
	@PONo_Consolidation int, 
	@AccNo varchar(6), 
	@CustomerOrLocation nvarchar(30), 
	@FinalDestination nvarchar(30), 
	@Description nvarchar(50), 
	@FactoryETD datetime, 
	@ShippingDate datetime, 
	@ShippingLine varchar(255), 
	@Vessel nvarchar(30), 
	@ContainerNo varchar(16), 
	@ETA datetime, 
	@Week int, 
	@ArrivalPort nvarchar(30), 
	@PI varchar(12), 
	@Payment char(1), 
	@DocReceived bit, 
	@CompletedDate datetime, 
	@BulkType varchar(2), 
	@ModifiedDate datetime 
AS

UPDATE [ShippingStatusReport] SET
	[REQ] = @REQ,
	[OrderDate] = @OrderDate,
	[ContainerSize] = @ContainerSize,
	[LP] = @LP,
	[PONo_Office] = @PONo_Office,
	[PONo_Type] = @PONo_Type,
	[PONo_Supplier] = @PONo_Supplier,
	[PONo_Number] = @PONo_Number,
	[IsPrimary] = @IsPrimary,
	[PONo_Consolidation] = @PONo_Consolidation,
	[AccNo] = @AccNo,
	[CustomerOrLocation] = @CustomerOrLocation,
	[FinalDestination] = @FinalDestination,
	[Description] = @Description,
	[FactoryETD] = @FactoryETD,
	[ShippingDate] = @ShippingDate,
	[ShippingLine] = @ShippingLine,
	[Vessel] = @Vessel,
	[ContainerNo] = @ContainerNo,
	[ETA] = @ETA,
	[Week] = @Week,
	[ArrivalPort] = @ArrivalPort,
	[PI] = @PI,
	[Payment] = @Payment,
	[DocReceived] = @DocReceived,
	[CompletedDate] = @CompletedDate,
	[BulkType] = @BulkType,
	[ModifiedDate] = @ModifiedDate
WHERE
	[ID] = @ID

GO
